{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Module Feedback\n",
    "\n",
    "This system records the responses of students on their learning experience at university.\n",
    "\n",
    "![rel](https://sqlzoo.net/w/images/7/71/Module-feedback.png)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "name": "stdin",
     "output_type": "stream",
     "text": [
      " ····\n"
     ]
    }
   ],
   "source": [
    "import getpass\n",
    "import psycopg2\n",
    "from sqlalchemy import create_engine\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "pwd = getpass.getpass()\n",
    "engine = create_engine(\n",
    "    'postgresql+psycopg2://postgres:%s@localhost/sqlzoo' % (pwd))\n",
    "pd.set_option('display.max_rows', 100)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 1. Find the student name from a matriculation number\n",
    "\n",
    "**Find the name of the student with number 50200100**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "insspr = pd.concat(list(pd.read_sql_table('INS_SPR', engine, chunksize=1000)))\n",
    "insmod = pd.concat(list(pd.read_sql_table('INS_MOD', engine, chunksize=1000)))\n",
    "insprs = pd.concat(list(pd.read_sql_table('INS_PRS', engine, chunksize=1000)))\n",
    "camsmo = pd.concat(list(pd.read_sql_table('CAM_SMO', engine, chunksize=1000)))\n",
    "insres = pd.concat(list(pd.read_sql_table('INS_RES', engine, chunksize=1000)))\n",
    "insque = pd.concat(list(pd.read_sql_table('INS_QUE', engine, chunksize=1000)))\n",
    "inscat = pd.concat(list(pd.read_sql_table('INS_CAT', engine, chunksize=1000)))\n",
    "\n",
    "# AYR_CODE length 7, so trim it\n",
    "camsmo['AYR_CODE'] = camsmo['AYR_CODE'].str.strip()\n",
    "insres['AYR_CODE'] = insres['AYR_CODE'].str.strip()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>SPR_FNM1</th>\n",
       "      <th>SPR_SURN</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Tom</td>\n",
       "      <td>Cotton</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  SPR_FNM1 SPR_SURN\n",
       "2      Tom   Cotton"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "insspr.loc[insspr['SPR_CODE']=='50200100', ['SPR_FNM1', 'SPR_SURN']]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 2. Find the modules studied by a student\n",
    "\n",
    "**Show the module code and module name for modules studied by the student with number 50200100 in session 2016/7 TR1**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>MOD_CODE</th>\n",
       "      <th>MOD_NAME</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>CSN08101</td>\n",
       "      <td>Systems and Services</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>132</th>\n",
       "      <td>INF08104</td>\n",
       "      <td>Database Systems</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>246</th>\n",
       "      <td>SET08108</td>\n",
       "      <td>Software Development 2</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     MOD_CODE                MOD_NAME\n",
       "2    CSN08101    Systems and Services\n",
       "132  INF08104        Database Systems\n",
       "246  SET08108  Software Development 2"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "(insmod.merge(camsmo, on='MOD_CODE')\n",
    " .query('(`SPR_CODE`==\"50200100\") & (`AYR_CODE`==\"2016/7\") & (`PSL_CODE`==\"TR1\")')\n",
    " [['MOD_CODE', 'MOD_NAME']])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 3. Find the modules and module leader studied by a student\n",
    "\n",
    "**Show the module code and module name and details of the module leader for modules studied by the student with number 50200100 in session 2016/7 TR1**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>MOD_CODE</th>\n",
       "      <th>MOD_NAME</th>\n",
       "      <th>PRS_CODE</th>\n",
       "      <th>PRS_FNM1</th>\n",
       "      <th>PRS_SURN</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>CSN08101</td>\n",
       "      <td>Systems and Services</td>\n",
       "      <td>40000008</td>\n",
       "      <td>James</td>\n",
       "      <td>Jackson</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>97</th>\n",
       "      <td>INF08104</td>\n",
       "      <td>Database Systems</td>\n",
       "      <td>40000036</td>\n",
       "      <td>Andrew</td>\n",
       "      <td>Cumming</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>246</th>\n",
       "      <td>SET08108</td>\n",
       "      <td>Software Development 2</td>\n",
       "      <td>40000408</td>\n",
       "      <td>Neil</td>\n",
       "      <td>Urquhart</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     MOD_CODE                MOD_NAME  PRS_CODE PRS_FNM1  PRS_SURN\n",
       "2    CSN08101    Systems and Services  40000008    James   Jackson\n",
       "97   INF08104        Database Systems  40000036   Andrew   Cumming\n",
       "246  SET08108  Software Development 2  40000408     Neil  Urquhart"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "(camsmo.merge(insmod, on='MOD_CODE')\n",
    " .merge(insprs, on=\"PRS_CODE\")\n",
    " .query('(SPR_CODE==\"50200100\") & (AYR_CODE==\"2016/7\") & (PSL_CODE==\"TR1\")')\n",
    " [['MOD_CODE', 'MOD_NAME', 'PRS_CODE', 'PRS_FNM1', 'PRS_SURN']])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 4. Show the scores for module SET08108\n",
    "\n",
    "**Show the Percentage of students who gave 4 or 5 to module SET08108 in session 2016/7 TR1**\n",
    "\n",
    "(note that this is not real data, these responses were randomly generated)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>QUE_CODE</th>\n",
       "      <th>QUE_TEXT</th>\n",
       "      <th>CAT_NAME</th>\n",
       "      <th>pct</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1.1</td>\n",
       "      <td>Staff are good at explaining things.</td>\n",
       "      <td>Learning and Teaching</td>\n",
       "      <td>89.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1.2</td>\n",
       "      <td>Staff made the subject interesting.</td>\n",
       "      <td>Learning and Teaching</td>\n",
       "      <td>82.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1.3</td>\n",
       "      <td>The module was intellectually stimulating.</td>\n",
       "      <td>Learning and Teaching</td>\n",
       "      <td>82.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1.4</td>\n",
       "      <td>The aims and objectives were clearly stated.</td>\n",
       "      <td>Learning and Teaching</td>\n",
       "      <td>89.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1.5</td>\n",
       "      <td>The module was well-organised and ran smoothly.</td>\n",
       "      <td>Learning and Teaching</td>\n",
       "      <td>78.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>1.6</td>\n",
       "      <td>The pace was appropriate.</td>\n",
       "      <td>Learning and Teaching</td>\n",
       "      <td>80.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>1.7</td>\n",
       "      <td>The level was appropriate.</td>\n",
       "      <td>Learning and Teaching</td>\n",
       "      <td>82.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>1.8</td>\n",
       "      <td>The workload was managable.</td>\n",
       "      <td>Learning and Teaching</td>\n",
       "      <td>78.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>1.9</td>\n",
       "      <td>I was able to contact module staff when I need...</td>\n",
       "      <td>Learning and Teaching</td>\n",
       "      <td>76.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>2.1</td>\n",
       "      <td>The assessment requirements were clearly stated.</td>\n",
       "      <td>Assessment and Feedback</td>\n",
       "      <td>84.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>2.2</td>\n",
       "      <td>The criteria for marking was made clear to me.</td>\n",
       "      <td>Assessment and Feedback</td>\n",
       "      <td>82.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>2.3</td>\n",
       "      <td>I was well supported for the assessment.</td>\n",
       "      <td>Assessment and Feedback</td>\n",
       "      <td>80.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>2.4</td>\n",
       "      <td>I was provided with feedback that aided unders...</td>\n",
       "      <td>Assessment and Feedback</td>\n",
       "      <td>80.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>2.5</td>\n",
       "      <td>Feedback was provided within three weeks of su...</td>\n",
       "      <td>Assessment and Feedback</td>\n",
       "      <td>80.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>3.1</td>\n",
       "      <td>The module was well supported by moodle.</td>\n",
       "      <td>Learning Resources</td>\n",
       "      <td>73.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>3.2</td>\n",
       "      <td>The library resources met my needs.</td>\n",
       "      <td>Learning Resources</td>\n",
       "      <td>82.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>3.3</td>\n",
       "      <td>The IT resources met my needs.</td>\n",
       "      <td>Learning Resources</td>\n",
       "      <td>89.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>3.4</td>\n",
       "      <td>The rooms/facilities were of good quality.</td>\n",
       "      <td>Learning Resources</td>\n",
       "      <td>78.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>4.1</td>\n",
       "      <td>I was statisfied with the module.</td>\n",
       "      <td>Overall</td>\n",
       "      <td>89.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   QUE_CODE                                           QUE_TEXT  \\\n",
       "0       1.1               Staff are good at explaining things.   \n",
       "1       1.2                Staff made the subject interesting.   \n",
       "2       1.3         The module was intellectually stimulating.   \n",
       "3       1.4       The aims and objectives were clearly stated.   \n",
       "4       1.5    The module was well-organised and ran smoothly.   \n",
       "5       1.6                          The pace was appropriate.   \n",
       "6       1.7                         The level was appropriate.   \n",
       "7       1.8                        The workload was managable.   \n",
       "8       1.9  I was able to contact module staff when I need...   \n",
       "9       2.1   The assessment requirements were clearly stated.   \n",
       "10      2.2     The criteria for marking was made clear to me.   \n",
       "11      2.3           I was well supported for the assessment.   \n",
       "12      2.4  I was provided with feedback that aided unders...   \n",
       "13      2.5  Feedback was provided within three weeks of su...   \n",
       "14      3.1           The module was well supported by moodle.   \n",
       "15      3.2                The library resources met my needs.   \n",
       "16      3.3                     The IT resources met my needs.   \n",
       "17      3.4         The rooms/facilities were of good quality.   \n",
       "18      4.1                  I was statisfied with the module.   \n",
       "\n",
       "                   CAT_NAME   pct  \n",
       "0     Learning and Teaching  89.0  \n",
       "1     Learning and Teaching  82.0  \n",
       "2     Learning and Teaching  82.0  \n",
       "3     Learning and Teaching  89.0  \n",
       "4     Learning and Teaching  78.0  \n",
       "5     Learning and Teaching  80.0  \n",
       "6     Learning and Teaching  82.0  \n",
       "7     Learning and Teaching  78.0  \n",
       "8     Learning and Teaching  76.0  \n",
       "9   Assessment and Feedback  84.0  \n",
       "10  Assessment and Feedback  82.0  \n",
       "11  Assessment and Feedback  80.0  \n",
       "12  Assessment and Feedback  80.0  \n",
       "13  Assessment and Feedback  80.0  \n",
       "14       Learning Resources  73.0  \n",
       "15       Learning Resources  82.0  \n",
       "16       Learning Resources  89.0  \n",
       "17       Learning Resources  78.0  \n",
       "18                  Overall  89.0  "
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "a = (insres\n",
    " .assign(score=np.floor(insres['RES_VALU']/4))\n",
    " .query('(MOD_CODE==\"SET08108\") & (AYR_CODE==\"2016/7\") & (PSL_CODE==\"TR1\")')\n",
    " .groupby('QUE_CODE')[['score']].agg(['sum', 'count']).reset_index())\n",
    "a['pct'] = round(100*a['score']['sum']/a['score']['count'], 0)\n",
    "a.columns = ['QUE_CODE', 's', 'n', 'pct']\n",
    "(a.merge(insque, on='QUE_CODE')\n",
    " .merge(inscat, on='CAT_CODE')\n",
    " [['QUE_CODE', 'QUE_TEXT', 'CAT_NAME', 'pct']])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 5. Show the frequency chart for module SET08108 for question 4.1\n",
    "\n",
    "**For each response 1-5 show the number of students who gave that response (Module SET08108, 2016/7, TR1)**\n",
    "\n",
    "(note that this is not real data, these responses were randomly generated)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>MOD_CODE</th>\n",
       "      <th>RES_VALU</th>\n",
       "      <th>SPR_CODE</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>SET08108</td>\n",
       "      <td>2</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>SET08108</td>\n",
       "      <td>4</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>SET08108</td>\n",
       "      <td>5</td>\n",
       "      <td>39</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   MOD_CODE  RES_VALU  SPR_CODE\n",
       "0  SET08108         2         6\n",
       "1  SET08108         4        10\n",
       "2  SET08108         5        39"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "(insres\n",
    " .query('(MOD_CODE==\"SET08108\") & (AYR_CODE==\"2016/7\") & (PSL_CODE==\"TR1\") & (QUE_CODE==\"4.1\")')\n",
    " [['MOD_CODE', 'RES_VALU', 'SPR_CODE']]\n",
    " .groupby(['MOD_CODE', 'RES_VALU'])\n",
    " .count().reset_index())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.7"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}
